package org.sibframework.sda;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

public class JDBC {

	private boolean autoCommit = true;
	private boolean printSql = false;
	private Connection connection;
	private DataSource dataSource = null;
	
	
	/****************************************************************************
	 * Common method Area
	 * 
	 ****************************************************************************/
	
	/**
	 * get current used connection
	 * @return Connection object current used.
	 */
	public Connection getConnection() {
		return connection;
	}

	/**
	 * close this instance
	 */
	public void close() {
		try {
			if (this.connection != null && !this.connection.isClosed()) {
				this.connection.close();
			}
			this.connection = null;
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	


	/**
	 * get the log status
	 * @return true if print SQL.
	 */
	public boolean isPrintSql() {
		return printSql;
	}

	/**
	 * set the log status
	 * @param printSql true if allow print SQL
	 */
	public void setPrintSql(boolean printSql) {
		this.printSql = printSql;
	}

	public JDBC() throws SQLException {
		super();
		dataSource = new ConnectionFactory();
		connection = dataSource.getConnection();
	}

	public JDBC(DataSource dataSource) throws SQLException {
		super();
		this.dataSource = dataSource;
		connection = dataSource.getConnection();
	}

	//is available
	public boolean isAvailable() {
		try {
			return this.getConnection().isValid(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	
	
	/*******************************************************************************
	 * Transaction Area
	 * 
	 *******************************************************************************/
	
	/**
	 * start a transaction
	 * @return save point object to roll back;
	 * @throws SQLException
	 */
	public Savepoint startTransaction() throws SQLException {
		getConnection().setAutoCommit(false);
		return getConnection().setSavepoint();
	}
	
	/**
	 * roll back a transaction
	 * @param point save point to roll back to.
	 * @throws SQLException
	 */
	public void rollbackTransaction(Savepoint point) throws SQLException {
		getConnection().rollback(point);
	}

	/**
	 * roll back a transaction
	 * @throws SQLException
	 */
	public void rollbackTransaction() throws SQLException {
		getConnection().rollback();
	}
	
	/**
	 * commit and finish a transaction.
	 * @throws SQLException
	 */
	public void finishTransaction() throws SQLException {
		getConnection().commit();
		getConnection().setAutoCommit(autoCommit);
	}
	
	
	
	/********************************************************************************************************
	 * Table help method Area
	 * 
	 ********************************************************************************************************/
	
	/**
	 * get columns(name, type and className) of a table
	 * @param tableName table name to get column.
	 * @return column informations array. each column information is a String array of[name, type, className];
	 */
	public String[][] getColumns(String tableName) {

		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement("select * from " + tableName + " where 1=2");
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			String[][] result = new String[rsmd.getColumnCount()][];
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				result[i - 1] = new String[3];
				result[i - 1][0] = rsmd.getColumnName(i);
				result[i - 1][1] = rsmd.getColumnTypeName(i);
				result[i - 1][2] = rsmd.getColumnClassName(i);
			}
			rs.close();
			pstm.close();
			return result;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			e.printStackTrace();
			
		}
		return null;
	}
	
	/**
	 * Get columns name and java type mapping of a table
	 * @param tableName table name to get column.
	 * @return column java type map.;
	 */
	public Map<String, String> getColumnJavaType(String tableName) {

		PreparedStatement pstm = null;
		ResultSet rs = null;
		Map<String, String> result = new HashMap<String, String>();
		try {
			pstm = getConnection().prepareStatement("select * from " + tableName + " where 1=2");
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			for (int i = 1; i <= rsmd.getColumnCount(); i++) {
				result.put(rsmd.getColumnName(i), rsmd.getColumnClassName(i));
			}
			rs.close();
			pstm.close();
			return result;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			e.printStackTrace();
			
		}
		return null;
	}
	
	
	
	/**********************************************************************************************
	 * Base JDBC method
	 * 
	 **********************************************************************************************/
	
	
	/**
	 * query custom SQL and use a visitor pattern to take care the result.
	 * @param sql String to query
	 * @param visitor ResultVisitor implement.
	 * @throws SQLException 
	 */
	public void query(String sql, ResultVisitor visitor) throws SQLException {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		if (visitor == null) {
			return;
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int ccount = rsmd.getColumnCount();
			String columns[] = new String[ccount];
			for (int i = 0; i < ccount; i ++) {
				columns[i] = rsmd.getColumnName(i + 1);
			}
			visitor.column(columns);
			
			while (rs.next()) {
				visitor.next();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					visitor.data(rsmd.getColumnName(i), rs.getObject(i));
				}
			}
			visitor.finish();
			rs.close();
			pstm.close();
			
			return;
		} catch (SQLException e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			System.err.println(e.getLocalizedMessage());
			throw e;
		}
	
	}
	
	
	
	/**
	 * query the custom SQL and return the object of results as maps
	 * @param sql String to query
	 */
	public List<Map<String, Object>> query2Map(String sql) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		
		List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
		
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			while (rs.next()) {
				Map<String, Object> result = new HashMap<String, Object>();
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					result.put(rsmd.getColumnName(i), rs.getObject(i));
				}
				results.add(result);
			}
			rs.close();
			pstm.close();
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
		return results;
	}


	/**
	 * query the custom SQL and return the first object of results as a map
	 * @param sql String to query
	 * @param result the result map.
	 */
	public void queryFirst2Map(String sql, Map<String, Object> result) {
		if (isPrintSql()) {
			System.out.println(sql);
		}
		if (result == null) {
			result = new HashMap<String, Object> ();
		}
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			if (rs.next()) {
				for (int i = 1; i <= rsmd.getColumnCount(); i++) {
					result.put(rsmd.getColumnName(i), rs.getObject(i));
				}
			}
			rs.close();
			pstm.close();
			//return result;
			
		} catch (Exception e) {
			try {
				rs.close();
			} catch (Exception e1) {}
			try {
				pstm.close();
			} catch (Exception e1) {}
			System.err.println(sql);
			e.printStackTrace();
			
		}
		//return null;
	}


}
